﻿Imports System.Data.SqlClient

Public Class FrmTimLoaiBienDongHoSoBienDong
    Private Sub NapLoaiDat1()
        'Dim tbl As DataTable
        'Dim dbAdapter As SqlDataAdapter
        'Dim dbComm As SqlCommand
        'dbComm = New SqlCommand(" select kyhieu, tenld from LOAIDAT", ob_cnn)
        'dbAdapter = New SqlDataAdapter(dbComm)
        'tbl = New DataTable()
        'dbAdapter.Fill(tbl)
        'CmbLD.DisplayMember = "kyhieu"
        'CmbLD.ValueMember = "kyhieu"
        'CmbLD.DataSource = tbl
        'ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbHuyen.DisplayMember = "T_huyen"
        CmbHuyen.ValueMember = "maH"
        CmbHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
   
    Private Sub NapLoaiBienDong()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from LOAIBD", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cbLoaiBienDong.DisplayMember = "TenLoaiBD"
        cbLoaiBienDong.ValueMember = "maBD"
        cbLoaiBienDong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    
    Private Sub CmbHuyen_SelectedIndexChanged(sender As Object, e As EventArgs)
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & CmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        CmbXa.DisplayMember = "TenXa"
        CmbXa.ValueMember = "maxa"
        CmbXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapVaoTexBoxThuaDat()
        'TextSoHoSo.DataBindings.Clear()
        'TextSoHoSo.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "sohoso")
        'TextSoThua.DataBindings.Clear()
        'TextSoThua.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "thuadat")
        'TextToBD.DataBindings.Clear()
        'TextToBD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "tobando")
        'TextDienTich.DataBindings.Clear()
        'TextDienTich.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Dientich")
        'CmbLD.DataBindings.Clear()
        'CmbLD.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Loaidat")
        'CmbLoaiTaiLieu.DataBindings.Clear()
        'CmbLoaiTaiLieu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenLoaitailieu")
        'TextTenChu.DataBindings.Clear()
        'TextTenChu.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenchu")
        'TextDiaChi.DataBindings.Clear()
        'TextDiaChi.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Diachi")
        'CmbTinhTrang.DataBindings.Clear()
        'CmbTinhTrang.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tentinhtrang")
        'cmbPhong.DataBindings.Clear()
        'cmbPhong.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenphong")
        'CmbNgan.DataBindings.Clear()
        'CmbNgan.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenngan")
        'CmbKe.DataBindings.Clear()
        'CmbKe.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "TenKe")
        'CmbKho.DataBindings.Clear()
        'CmbKho.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "Tenkho")
        'TextMaBienDong.DataBindings.Clear()
        'TextMaBienDong.DataBindings.Add("Text", DataGridViewThuaDat.DataSource, "MaBienDong")

    End Sub
    Private Sub NapDataGridViewThuaDat()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select thua.sohoso, thua.thuadat, thua.tobando, thua.dientich, thua.loaidat, tl.tenloaitailieu, " &
                                "ten.Tenchu, ten.Diachi, tt.tentinhtrang, p. tenphong , ng.Tenngan, k.tenke, kh.tenkho, MaBienDong , lbd.TenLoaiBD" &
                               " From THUADATBIENDONG thua, TENCHUBIENDONG ten, DMTailieu tl, DMtinhtrang tt, LoaiBD lbd, DMPhong p, DMNgan ng, DMke k, DMkho kh  " &
                               " WHERE thua.xaid = '" & CmbXa.SelectedValue.ToString & "' " &
                               " and thua.huyenid = '" & CmbHuyen.SelectedValue.ToString & "' " &
                               " and thua.loaitailieu = tl.maloaitailieuID " &
                               " and thua.sohoso = ten.sohoso " &
                               " and ten.tinhtrangid = tt.matinhtrangid " &
                               " and ten.LoaiBienDongID = lbd.MaBD " &
                               " and ten.LoaiBienDongID = '" & cbLoaiBienDong.SelectedValue.ToString & "'" &
                               " and ten.phongid = p.maphongid " &
                               " and ten.nganid = ng.manganid " &
                               " and ten.keid = k.makeid " &
                               " and ten.khoid = kh.makhoid ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewThuaDat.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub ButtonTimKiem_Click(sender As System.Object, e As System.EventArgs) Handles ButtonTimKiem.Click
        NapDataGridViewThuaDat()
    End Sub

    Private Sub FrmTimLoaiBienDongHoSoBienDong_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        NapLoaiBienDong()
        NapDonViHuyen()
        DataGridViewThuaDat.RowsDefaultCellStyle.BackColor = Color.Beige
        DataGridViewThuaDat.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
    End Sub

    Private Sub ButtonThoat_Click(sender As System.Object, e As System.EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub

    Private Sub DataGridViewThuaDat_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellClick
        NapVaoTexBoxThuaDat()
    End Sub

    Private Sub DataGridViewThuaDat_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewThuaDat.CellContentClick

    End Sub

    Private Sub DataGridViewThuaDat_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewThuaDat.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewThuaDat.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewThuaDat.RowHeadersWidth < Size.Width + 20 Then DataGridViewThuaDat.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))
    End Sub
    Private Sub XuatExcel()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = "Ho so nhap"
                Dim i As Integer = 1
                For col = 0 To DataGridViewThuaDat.Columns.Count - 1
                    .cells(1, i).value = DataGridViewThuaDat.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To DataGridViewThuaDat.Columns.Count - 1
                    i = 2
                    For row = 0 To DataGridViewThuaDat.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = DataGridViewThuaDat.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã được xuất sang định dạng Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub

    Private Sub ButtonXuatExcel_Click(sender As Object, e As EventArgs) Handles ButtonXuatExcel.Click
        XuatExcel()
    End Sub

    Private Sub cmbHuyen_SelectedIndexChanged_1(sender As Object, e As EventArgs) Handles cmbHuyen.SelectedIndexChanged
        ' load ten xa
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & cmbHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        cmbxa.DisplayMember = "TenXa"
        cmbxa.ValueMember = "maxa"
        cmbxa.DataSource = tbl
        ob_cnn.Close()
    End Sub
End Class